package com.menhai.personmanagement.controller;

import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.menhai.personmanagement.entity.Codelist;
import com.menhai.personmanagement.entity.MbPopulation;
import com.menhai.personmanagement.entity.co.MbPopulationCo;
import com.menhai.personmanagement.entity.vo.FrontPageVo;
import com.menhai.personmanagement.entity.vo.MbPopulationTemplete;
import com.menhai.personmanagement.entity.vo.MbPopulationVo;
import com.menhai.personmanagement.entity.vo.SaveArchivesVo;
import com.menhai.personmanagement.plus.OperLog;
import com.menhai.personmanagement.services.ICodelistService;
import com.menhai.personmanagement.services.IMbPopulationService;
import com.menhai.personmanagement.utils.ExportSysUserInfoExcel;
import lombok.extern.slf4j.Slf4j;
import net.sourceforge.pinyin4j.PinyinHelper;
import net.sourceforge.pinyin4j.format.HanyuPinyinCaseType;
import net.sourceforge.pinyin4j.format.HanyuPinyinOutputFormat;
import net.sourceforge.pinyin4j.format.HanyuPinyinToneType;
import net.sourceforge.pinyin4j.format.exception.BadHanyuPinyinOutputFormatCombination;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.annotations.Param;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import static com.menhai.personmanagement.utils.ExcelOperUtil.addSelect;

/**
 * <p>
 *  前端控制器
 * </p>
 *
 * @author shikun
 * @since 2021-12-02
 */

@Slf4j
@RestController
@RequestMapping("/mb-population")
public class MbPopulationController {

     @Autowired
     private IMbPopulationService mbPopulationService;

     @Autowired
     private ICodelistService codelistService;
     /**
      * @describe 心电图 根据 村民id 查询 村民
      * @param mbPopulation
      * @Return com.menhai.personmanagement.entity.MbPopulation
      * @Date 2022/5/16 9:11
      * @author shikun
      */
     @RequestMapping("/selectEcgBypopulationId")
     public MbPopulation selectEcgBypopulationId(@RequestBody MbPopulation mbPopulation){
          return mbPopulationService.selectEcgBypopulationId(mbPopulation);
     }

     @OperLog(operDesc = "添加用户",operType = "插入")
     @RequestMapping("/addPerson")
     public int addPerson(@RequestBody MbPopulationVo mbPopulationVo) throws BadHanyuPinyinOutputFormatCombination {
          mbPopulationVo.setIsAvailable(1);
          //获取拼音首字母
          HanyuPinyinOutputFormat format = new HanyuPinyinOutputFormat();
          //拼音大写
          format.setCaseType(HanyuPinyinCaseType.UPPERCASE);
          //不带声调
          format.setToneType(HanyuPinyinToneType.WITHOUT_TONE);
          String letter = PinyinHelper.toHanYuPinyinString(mbPopulationVo.getName(), format, ",", true).substring(0,1);
          mbPopulationVo.setFirstLetter(StringUtils.upperCase(letter));
          return mbPopulationService.addPopluation(mbPopulationVo);
     }

     /**
      * @describe 下级村民
      * @param superiorCode
      * @Return java.util.List<com.menhai.personmanagement.entity.vo.FrontPageVo>
      * @Date 2022/2/25 13:13
      * @author shikun
      */
     @RequestMapping("/selectresidentlist")
     public List<MbPopulation> selectresidentlist(@Param("superiorCode") String superiorCode){
          return mbPopulationService.selectresidentlist(superiorCode);
     }


     /**
      * @describe id查询村民
      * @param id
      * @Return com.menhai.personmanagement.entity.MbPopulation
      * @Date 2022/2/25 13:23
      * @author shikun
      */
     @RequestMapping("/searchByStringId")
     public MbPopulation searchByStringId(@Param("id") String id){
          return mbPopulationService.searchByStringId(id);
     }

     /**
      * @describe id查询村民导出
      * @param response
      * @param id
      * @Return void
      * @Date 2022/2/25 14:21
      * @author shikun
      */
     @RequestMapping("/exportByStringId")
     public void exportTjpcStatistics(HttpServletResponse response,@Param("id") String id) throws Exception{
          try {
               MbPopulation mbPopulation = mbPopulationService.searchByStringId(id);
               ServletOutputStream out = response.getOutputStream();
               new ExportSysUserInfoExcel().searchByStringId(mbPopulation, out, response);
          } catch (IOException e) {
               e.printStackTrace();
          }
     }

     /**
      * @describe 残疾人员统计表
      * @param mbPopulationCo
      * @Return com.baomidou.mybatisplus.core.metadata.IPage<com.menhai.personmanagement.entity.MbPopulation>
      * @Date 2022/2/21 10:47
      * @author shikun
      */
     @RequestMapping("/selectBydisabled")
     public IPage<MbPopulation> selectBydisabled(MbPopulationCo mbPopulationCo){
          IPage<MbPopulation> list = mbPopulationService.selectBydisabled(mbPopulationCo);
          return list;
     }

     /**
      * @describe 残疾人员导出
      * @param response
      * @param mbPopulationCo
      * @Return void
      * @Date 2022/2/21 13:54
      * @author shikun
      */
     @RequestMapping("/exportBydisabled")
     public void exportBydisabled(HttpServletResponse response,MbPopulationCo mbPopulationCo) {
          try {
               List<MbPopulation> list = mbPopulationService.exportBydisabled(mbPopulationCo);
               ServletOutputStream out = response.getOutputStream();
               new ExportSysUserInfoExcel().exportBydisabled(list, out, response);
          } catch (IOException e) {
               e.printStackTrace();
          }
     }

     /**
      * @describe 两劳人员统计表
      * @param mbPopulationCo
      * @Return com.baomidou.mybatisplus.core.metadata.IPage<com.menhai.personmanagement.entity.MbPopulation>
      * @Date 2022/2/21 13:54
      * @author shikun
      */
     @RequestMapping("/selectBylianglao")
     public IPage<MbPopulation> selectBylianglao(MbPopulationCo mbPopulationCo){
          IPage<MbPopulation> list = mbPopulationService.selectBylianglao(mbPopulationCo);
          return list;
     }

     /**
      * @describe 两劳人员导出
      * @param response
      * @param mbPopulationCo
      * @Return void
      * @Date 2022/2/21 14:48
      * @author shikun
      */
     @RequestMapping("/exportBylianglao")
     public void exportBylianglao(HttpServletResponse response,MbPopulationCo mbPopulationCo) {
          try {
               List<MbPopulation> list = mbPopulationService.exportBylianglao(mbPopulationCo);
               ServletOutputStream out = response.getOutputStream();
               new ExportSysUserInfoExcel().selectBylianglao(list, out, response);
          } catch (IOException e) {
               e.printStackTrace();
          }
     }

     /**
      * @describe 军烈属信息
      * @param mbPopulationCo
      * @Return com.baomidou.mybatisplus.core.metadata.IPage<com.menhai.personmanagement.entity.MbPopulation>
      * @Date 2022/2/21 15:05
      * @author shikun
      */
     @RequestMapping("/selectByjunlieshu")
     public IPage<MbPopulation> selectByjunlieshu(MbPopulationCo mbPopulationCo){
          IPage<MbPopulation> list = mbPopulationService.selectByjunlieshu(mbPopulationCo);
          return list;
     }


     @RequestMapping("/gecunrenshu")
     public List gecunrenshu(@Param("state") String state,@Param("local") String local){
          return mbPopulationService.gecunrenshu(state,local);
     }

     @RequestMapping("/gecunren")
     public List gecunren(@Param("state") String state){
          return mbPopulationService.gecunren();
     }

     @RequestMapping("/xianshicun")
     public List xianshicun(@Param("hosnum") String hosnum){
          return mbPopulationService.xianshicun(hosnum);
     }

     @RequestMapping("/usergecunrenshu")
     public List usergecunrenshu(){
          return mbPopulationService.usergecunrenshu();     }

     @RequestMapping("/codelistallin")
     public List codelistallin(){
          return mbPopulationService.codelistallin();     }

     @RequestMapping("/codelistBycodelistId")
     public List codelistBycodelistId(@Param("hosnum") String hosnum){
          return mbPopulationService.codelistBycodelistId(hosnum);
     }

     @RequestMapping("/codeListBykindCode")
     public List codeListBykindCode(){
          return mbPopulationService.codeListBykindCode();
     }


     @RequestMapping("/healthPlangecunrenshu")
     public List healthPlangecunrenshu(){
          return mbPopulationService.healthPlangecunrenshu();
     }
 
     @RequestMapping("/usergecunrenshuAndisAvailable")
     public List usergecunrenshuAndisAvailable(){
          return mbPopulationService.usergecunrenshuAndisAvailable();
     }


     @RequestMapping("/reportcardtree")
     public List reportcardtree(){
          return mbPopulationService.reportcardtree();
     }
 
     /**
      * @describe 军烈属信息导出
      * @param response
      * @param mbPopulationCo
      * @Return void
      * @Date 2022/2/21 15:20
      * @author shikun
      */
     @RequestMapping("/exportByjunlieshu")
     public void exportByjunlieshu(HttpServletResponse response,MbPopulationCo mbPopulationCo) {
          try {
               List<MbPopulation> list = mbPopulationService.exportByjunlieshu(mbPopulationCo);
               ServletOutputStream out = response.getOutputStream();
               new ExportSysUserInfoExcel().exportByjunlieshu(list, out, response);
          } catch (IOException e) {
               e.printStackTrace();
          }
     }

     /**
      * @describe 医保人员信息
      * @param mbPopulationCo
      * @Return com.baomidou.mybatisplus.core.metadata.IPage<com.menhai.personmanagement.entity.MbPopulation>
      * @Date 2022/2/21 15:57
      * @author shikun
      */
     @RequestMapping("/selectBymedicalInsurance")
     public IPage<MbPopulation> selectBymedicalInsurance(MbPopulationCo mbPopulationCo){
          IPage<MbPopulation> list = mbPopulationService.selectBymedicalInsurance(mbPopulationCo);
          return list;
     }

     /**
      * @describe 参保人员信息导出
      * @param response
      * @param mbPopulationCo
      * @Return void
      * @Date 2022/2/21 16:03
      * @author shikun
      */
     @RequestMapping("/exportBymedicalInsurance")
     public void exportBymedicalInsurance(HttpServletResponse response,MbPopulationCo mbPopulationCo) {
          try {
               List<MbPopulation> list = mbPopulationService.exportBymedicalInsurance(mbPopulationCo);
               ServletOutputStream out = response.getOutputStream();
               new ExportSysUserInfoExcel().exportBymedicalInsurance(list, out, response);
          } catch (IOException e) {
               e.printStackTrace();
          }
     }

     @OperLog(operDesc = "编辑时查询",operType = "查询")
     @RequestMapping("/findHouseNumber")
     public List findHouseNumber(@Param("houseNumber") String houseNumber,@RequestHeader("userid") String userid,@RequestHeader("level") String level){
          return mbPopulationService.findHouseNumber(houseNumber, level, userid);
     }

     @OperLog(operDesc = "获取用户",operType = "查询")
     @RequestMapping("/getPopulation")
     public Page<MbPopulation> getPopulation(@Param("pagesize") int pagesize,@Param("size") int size,@Param("levelnum") String levelnum){
          return mbPopulationService.getPopulation(pagesize,size,levelnum);
     }

     @OperLog(operDesc = "根据身份证查询",operType = "查询")
     @RequestMapping("/searchByIdnumber")
     public MbPopulation searchByIdnumber(@Param("idnumber") String idnumber){
          return mbPopulationService.searchByIdnumber(idnumber);
     }

     @RequestMapping("/searchByUserName")
     public List searchByUserName(@RequestHeader("userid") String userid,@RequestHeader("level") String level, @Param("name") String name,@Param("menpai") String menpai){
          return mbPopulationService.searchByUserName(userid, level, name, menpai);
     }


     @OperLog(operDesc = "删除用户",operType = "删除")
     @RequestMapping("/deletePopulation")
     public int deletePopulation(@Param("id") int id){
          return mbPopulationService.deletePopulation(id);
     }


     @OperLog(operDesc = "注销或者启用",operType = "修改")
     @RequestMapping("/isAvailable")
     public int isAvailable(MbPopulation mbPopulation){
          return mbPopulationService.isAvailable(mbPopulation);
     }

     @OperLog(operDesc = "修改信息",operType = "修改")
     @RequestMapping("/updatePopulation")
     public int updatePopulation(@RequestBody MbPopulationVo mbPopulationVo)throws BadHanyuPinyinOutputFormatCombination{
          //获取拼音首字母
          HanyuPinyinOutputFormat format = new HanyuPinyinOutputFormat();
          //拼音大写
          format.setCaseType(HanyuPinyinCaseType.UPPERCASE);
          //不带声调
          format.setToneType(HanyuPinyinToneType.WITHOUT_TONE);
          String letter = PinyinHelper.toHanYuPinyinString(mbPopulationVo.getName(), format, ",", true).substring(0,1);
          mbPopulationVo.setFirstLetter(StringUtils.upperCase(letter));
          return mbPopulationService.updataPopulation(mbPopulationVo);
     }

     @RequestMapping("/searchPopulation")
     public Page<MbPopulation> searchPopulation(@Param("name") String name,@Param("sex") String sex,@Param("age1") String age1,@Param("age2") String age2,@Param("idnumber") String idnumber,@Param("tell") String tell,@Param("state") String state,@Param("pagesize") int pagesize,@Param("size") int size,@Param("levelnum") String levelnum,@Param("chronic") String chronic,@Param("zhen") String zhen,@Param("cun") String cun,@Param("houseNumber") String houseNumber,@Param("hosnumarray") String hosnumarray,@Param("local") String local,@Param("familSearch") String familSearch,@Param("id") String id){
          return mbPopulationService.searchPopulation(name,sex,age1,age2,idnumber,tell,state,pagesize,size,levelnum,chronic,zhen,cun,houseNumber,hosnumarray,local,familSearch,id);
     }

     @RequestMapping("/searchByuserId")
     public MbPopulation searchByuserId(@Param("id") Long id ,@Param("idnumber") String idnumber){
          return mbPopulationService.searchByuserId(id,idnumber);
     }


     /**
      * @describe 村民列表
      * @param mbPopulationCo
      * @Return com.baomidou.mybatisplus.core.metadata.IPage<com.menhai.personmanagement.entity.MbPopulation>
      * @Date 2021/12/13 15:32
      * @author shikun
      */
     @RequestMapping("/selectByList")
     public IPage<MbPopulation> selectByList(MbPopulationCo mbPopulationCo){
          return mbPopulationService.selectByList(mbPopulationCo);
     }

     @OperLog(operDesc = "修改状态",operType = "修改")
     @RequestMapping("/updateAvailableVal")
     public int updateAvailableVal(@Param("id") int id,@Param("isAvailableVal") int isAvailableVal){
          return mbPopulationService.updateAvailableVal(id,isAvailableVal);
     }


     @RequestMapping("/findFamilyData")
     public List findFamilyData(@Param("familyCode") String familyCode,@Param("id") int id){
          return mbPopulationService.findFamilyData(familyCode,id);
     }

     @RequestMapping("/findFamily")
     public List findFamily(@Param("huhao") String huhao){
          return mbPopulationService.findFamily(huhao);
     }

     @RequestMapping("/addressList")
     public Page addressList(@Param("pagesize") int pagesize,@Param("pagenum") int pagenum,@RequestHeader("level") String level,@Param("name") String name){
          return mbPopulationService.addressList(pagesize, pagenum, level,name);
     }


     @RequestMapping("/lowIncome")
     public Page lowIncome(@RequestHeader("level") String level,@Param("pagesize") int pagesize,@Param("pagenum") int pagenum,@Param("name") String name){
          return mbPopulationService.lowIncome(level,pagesize,pagenum, name);
     }

     @RequestMapping("/unemployment")
     public Page unemployment(@RequestHeader("level") String level,@Param("pagesize") int pagesize,@Param("pagenum") int pagenum,@Param("name") String name){
          return mbPopulationService.unemployment(level, pagesize, pagenum, name);
     }

     @RequestMapping("/youfu")
     public List youfu(@RequestHeader("level") String level,@Param("date") String date){
          return mbPopulationService.exprotYoufu(level, date);
     }


     @RequestMapping("/census")
     public Page census(@RequestHeader("level") String level,@Param("pagesize") int pagesize, @Param("pagenum") int pagenum,@Param("minzu") String minzu,@Param("xueli") String xueli,@Param("hunyin") String hunyin,@Param("yibao") String yibao,@Param("jiankang") String jiankang){
          return mbPopulationService.persontotal(level, pagesize,pagenum,minzu,xueli,hunyin,yibao,jiankang);
     }
     /**
      * @describe 根据id 查询村民
      * @param mbPopulation
      * @Return com.menhai.personmanagement.entity.MbPopulation
      * @Date 2021/12/14 11:11
      * @author shikun
      */
     @RequestMapping("/selectByid")
     public MbPopulation selectByid(MbPopulation mbPopulation){
          return mbPopulationService.selectByid(mbPopulation);
     }


     /**
      * @describe 首页数据
      * @param
      * @Return com.menhai.personmanagement.entity.vo.FrontPageVo
      * @Date 2021/12/20 9:28
      * @author shikun
      */
     @RequestMapping("/frontpagenumber")
     public FrontPageVo frontpagenumber(@Param("superiorCode") String superiorCode){
          return mbPopulationService.frontpagenumber(superiorCode);
     }


     /**
      * @describe 年龄分类
      * @param
      * @Return java.util.List<com.menhai.personmanagement.entity.vo.FrontPageVo>
      * @Date 2021/12/20 10:45
      * @author shikun
      */
     @RequestMapping("/ageclassification")
     public List<FrontPageVo> ageclassification(@Param("superiorCode") String superiorCode){
          return mbPopulationService.ageclassification(superiorCode);
     }

     /**
      * @describe 健康比例
      * @param superiorCode
      * @Return java.util.List<com.menhai.personmanagement.entity.vo.FrontPageVo>
      * @Date 2022/1/21 14:01
      * @author shikun
      */
     @RequestMapping("/healthyratio")
     public List<FrontPageVo> healthyratio(@Param("superiorCode") String superiorCode){
          return mbPopulationService.healthyratio(superiorCode);
     }

     /**
      * @describe 性别分类
      * @param
      * @Return java.util.List<com.menhai.personmanagement.entity.vo.FrontPageVo>
      * @Date 2021/12/20 11:39
      * @author shikun
      */
     @RequestMapping("/genderclassification")
     public List<FrontPageVo> genderclassification(@Param("superiorCode") String superiorCode){
          return mbPopulationService.genderclassification(superiorCode);
     }

     /**
      * @describe 人口来源分类
      * @param
      * @Return java.util.List<com.menhai.personmanagement.entity.vo.FrontPageVo>
      * @Date 2021/12/20 12:00
      * @author shikun
      */
     @RequestMapping("/sourceclassification")
     public List<FrontPageVo> sourceclassification(@Param("superiorCode") String superiorCode){
          return mbPopulationService.sourceclassification(superiorCode);
     }


     @RequestMapping("/exprotCensus")
     public void exprotCensus(HttpServletResponse response, @Param("level") String level,@Param("minzu") String minzu,@Param("xueli") String xueli,@Param("hunyin") String hunyin,@Param("yibao") String yibao,@Param("jiankang") String jiankang){
          try {
               List<MbPopulation> list = mbPopulationService.exprotCensus(level,minzu,xueli,hunyin,yibao,jiankang);
               ExcelWriter writer = ExcelUtil.getWriter();
               writer.addHeaderAlias("name", "姓名");
               writer.addHeaderAlias("age", "年龄");
               writer.addHeaderAlias("sex", "性别");
               writer.addHeaderAlias("idnumber", "身份证号");
               writer.addHeaderAlias("marriage", "婚姻状况");
               writer.addHeaderAlias("telephony", "联系电话");
               writer.addHeaderAlias("nation", "民族");
               writer.addHeaderAlias("newaddress", "现住地址");
               writer.addHeaderAlias("education", "学历");
               writer.addHeaderAlias("medicalInsurance", "医保");
               writer.addHeaderAlias("health", "健康状况");

               writer.setOnlyAlias(true);
               writer.write(list,true);
               response.setContentType("application/vnd.ms-excel;charset=utf-8");
               String fileName = new String("人口综合查询统计表".getBytes(StandardCharsets.UTF_8),StandardCharsets.ISO_8859_1);
               response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
               ServletOutputStream out = null;
               try {
                    out = response.getOutputStream();
                    writer.flush(out, true);
               } catch (IOException e) {
                    e.printStackTrace();
               } finally {
                    // 关闭writer，释放内存
                    writer.close();
               }
               //此处记得关闭输出Servlet流
               IoUtil.close(out);

          } catch (Exception e) {
               e.printStackTrace();
          }
     }

     @RequestMapping("/exprotDialectical")
     public void exprotDialectical(HttpServletResponse response, @Param("level") String level){
          try {
               List<MbPopulation> list = new ArrayList<>();
               ExcelWriter writer = ExcelUtil.getWriter();
               writer.addHeaderAlias("cun", "村名");
               writer.addHeaderAlias("三属", "平和质");
               writer.addHeaderAlias("在乡老复员军人", "气虚质");
               writer.addHeaderAlias("三属", "阳虚质");
               writer.addHeaderAlias("在乡老复员军人", "阴虚质");
               writer.addHeaderAlias("三属", "痰湿质");
               writer.addHeaderAlias("在乡老复员军人", "湿热质");
               writer.addHeaderAlias("三属", "血瘀质");
               writer.addHeaderAlias("在乡老复员军人", "气郁质");
               writer.addHeaderAlias("三属", "痰湿质兼血瘀质");
               writer.addHeaderAlias("在乡老复员军人", "倾向气郁质");
               writer.addHeaderAlias("三属", "平和倾向阳虚质");
               writer.addHeaderAlias("在乡老复员军人", "接近平和质");
               writer.addHeaderAlias("三属", "平和倾向阴虚质");
               writer.addHeaderAlias("在乡老复员军人", "阴虚质兼血瘀质");
               writer.addHeaderAlias("三属", "阴虚质兼痰湿质");
               writer.addHeaderAlias("在乡老复员军人", "阴虚质兼湿热质");
               writer.addHeaderAlias("三属", "阴虚质兼气郁质");
               writer.setOnlyAlias(true);
               writer.write(list,true);
               response.setContentType("application/vnd.ms-excel;charset=utf-8");
               String fileName = new String("辩证查询".getBytes(StandardCharsets.UTF_8),StandardCharsets.ISO_8859_1);
               response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
               ServletOutputStream out = null;
               try {
                    out = response.getOutputStream();
                    writer.flush(out, true);
               } catch (IOException e) {
                    e.printStackTrace();
               } finally {
                    // 关闭writer，释放内存
                    writer.close();
               }
               //此处记得关闭输出Servlet流
               IoUtil.close(out);

          } catch (Exception e) {
               e.printStackTrace();
          }
     }

     /**
      * 导出优抚人员
      * @param response
      * @param level
      */
     @RequestMapping("/exprotYoufu")
     public void exprotYoufu(HttpServletResponse response, @Param("level") String level,@Param("date") String date){
          try {
               List<MbPopulation> list = mbPopulationService.exprotYoufu(level, date);
               ExcelWriter writer = ExcelUtil.getWriter();
               writer.addHeaderAlias("cun", "村名");
               writer.addHeaderAlias("三属", "三属");
               writer.addHeaderAlias("在乡老复员军人", "在乡老复员军人");
               writer.addHeaderAlias("带病回乡退伍军人", "带病回乡退伍军人");
               writer.addHeaderAlias("两参人员", "两参人员");
               writer.addHeaderAlias("伤残军人", "伤残军人");
               writer.addHeaderAlias("60周岁农村籍退役士兵", "60周岁农村籍退役士兵");
               writer.setOnlyAlias(true);
               writer.write(list,true);
               response.setContentType("application/vnd.ms-excel;charset=utf-8");
               String fileName = new String("优抚人员统计表".getBytes(StandardCharsets.UTF_8),StandardCharsets.ISO_8859_1);
               response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
               ServletOutputStream out = null;
               try {
                    out = response.getOutputStream();
                    writer.flush(out, true);
               } catch (IOException e) {
                    e.printStackTrace();
               } finally {
                    // 关闭writer，释放内存
                    writer.close();
               }
               //此处记得关闭输出Servlet流
               IoUtil.close(out);

          } catch (Exception e) {
               e.printStackTrace();
          }
     }

     /**
      * 失业人员统计表
      * @param response
      * @param level
      */
     @RequestMapping("/exprotUnemployment")
     public void exprotUnemployment(HttpServletResponse response, @Param("level") String level,@Param("name") String name){
          try {
               List<MbPopulation> list = mbPopulationService.exprotUnemployment(level,name);
               ExcelWriter writer = ExcelUtil.getWriter();
               writer.addHeaderAlias("superiorCode", "村名");
               writer.addHeaderAlias("name", "姓名");
               writer.addHeaderAlias("age", "年龄");
               writer.addHeaderAlias("sex", "性别");
               writer.addHeaderAlias("idnumber", "身份证号");
               writer.addHeaderAlias("telephony", "联系电话");
               writer.addHeaderAlias("marriage", "婚姻状态");
               writer.addHeaderAlias("education", "文化程度");
               writer.addHeaderAlias("health", "健康状况");
               writer.setOnlyAlias(true);
               writer.write(list,true);
               response.setContentType("application/vnd.ms-excel;charset=utf-8");
               String fileName = new String("失业人员统计表".getBytes(StandardCharsets.UTF_8),StandardCharsets.ISO_8859_1);
               response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
               ServletOutputStream out = null;
               try {
                    out = response.getOutputStream();
                    writer.flush(out, true);
               } catch (IOException e) {
                    e.printStackTrace();
               } finally {
                    // 关闭writer，释放内存
                    writer.close();
               }
               //此处记得关闭输出Servlet流
               IoUtil.close(out);

          } catch (Exception e) {
               e.printStackTrace();
          }
     }

     /**
      * 导出低保户
      * @param response
      * @param level
      */
     @RequestMapping("/exprotLowIncome")
     public void exprotLowIncome(HttpServletResponse response, @Param("level") String level,@Param("name") String name){
          try {
               List<MbPopulation> list = mbPopulationService.exprotLowIncome(level, name);
               ExcelWriter writer = ExcelUtil.getWriter();
               writer.addHeaderAlias("superiorCode", "村名");
               writer.addHeaderAlias("name", "姓名");
               writer.addHeaderAlias("sex", "性别");
               writer.addHeaderAlias("idnumber", "身份证号");
//               writer.addHeaderAlias("familyNumber", "户号");
//               writer.addHeaderAlias("telephony", "联系电话");
               writer.setOnlyAlias(true);
               writer.write(list,true);
               response.setContentType("application/vnd.ms-excel;charset=utf-8");
               String fileName = new String("低保户统计表".getBytes(StandardCharsets.UTF_8),StandardCharsets.ISO_8859_1);
               response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
               ServletOutputStream out = null;
               try {
                    out = response.getOutputStream();
                    writer.flush(out, true);
               } catch (IOException e) {
                    e.printStackTrace();
               } finally {
                    // 关闭writer，释放内存
                    writer.close();
               }
               //此处记得关闭输出Servlet流
               IoUtil.close(out);

          } catch (Exception e) {
               e.printStackTrace();
          }
     }
     /**
      * 导出通讯录
      * @param response
      * @param level
      */
     @RequestMapping("/exprotAddressList")
     public void exprotAddressList(HttpServletResponse response,@Param("level") String level,@Param("name") String name){
          try {
               List<MbPopulation> list = mbPopulationService.exprotAddressList(level,name);
               ExcelWriter writer = ExcelUtil.getWriter();
               writer.addHeaderAlias("superiorCode", "村名");
               writer.addHeaderAlias("name", "姓名");
               writer.addHeaderAlias("sex", "性别");
               writer.addHeaderAlias("newaddress", "住址");
               writer.addHeaderAlias("familyNumber", "户号");
               writer.addHeaderAlias("telephony", "联系电话");
               writer.setOnlyAlias(true);
               writer.write(list,true);
               response.setContentType("application/vnd.ms-excel;charset=utf-8");
               String fileName = new String("居民通讯录".getBytes(StandardCharsets.UTF_8),StandardCharsets.ISO_8859_1);
               response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
               ServletOutputStream out = null;
               try {
                    out = response.getOutputStream();
                    writer.flush(out, true);
               } catch (IOException e) {
                    e.printStackTrace();
               } finally {
                    // 关闭writer，释放内存
                    writer.close();
               }
               //此处记得关闭输出Servlet流
               IoUtil.close(out);

          } catch (Exception e) {
               e.printStackTrace();
          }
     }

     /**
      * 导出家庭信息
      * @param huhao 户号
      */
     @RequestMapping("/exprotFamilyInfo")
     public void exprotFamilyInfo(HttpServletResponse response,@Param("huhao") String huhao){
          try {
               List list = mbPopulationService.findFamily(huhao);

               ExcelWriter writer = ExcelUtil.getWriter();
               writer.addHeaderAlias("superiorCode", "村名");
               writer.addHeaderAlias("name", "姓名");
               writer.addHeaderAlias("familyrole", "家庭角色");
               writer.addHeaderAlias("sex", "性别");
               writer.addHeaderAlias("birthday", "出生日期");
               writer.addHeaderAlias("marriage", "婚姻状态");
               writer.addHeaderAlias("education", "文化程度");

               writer.setOnlyAlias(true);
               writer.write(list,true);
               response.setContentType("application/vnd.ms-excel;charset=utf-8");
               String fileName = new String("家庭信息表".getBytes(StandardCharsets.UTF_8),StandardCharsets.ISO_8859_1);
               response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
               ServletOutputStream out = null;
               try {
                    out = response.getOutputStream();
                    writer.flush(out, true);
               } catch (IOException e) {
                    e.printStackTrace();
               } finally {
                    // 关闭writer，释放内存
                    writer.close();
               }
               //此处记得关闭输出Servlet流
               IoUtil.close(out);

          } catch (Exception e) {
               e.printStackTrace();
          }
     }

     /**
      * 导出人口excel
      * @param response
      * @param name
      * @param sex
      * @param age1
      * @param age2
      * @param idnumber
      * @param phone
      * @param state
      * @param levelnum
      */
     @OperLog(operDesc = "导出人口excel",operType = "导出")
     @RequestMapping("/exportPopulation")
     public void exportPopulation(HttpServletResponse response,@Param("name") String name,@Param("sex") String sex,@Param("age1") String age1,@Param("age2") String age2,@Param("idnumber") String idnumber,@Param("phone") String phone,@Param("state") String state,@Param("local") String local,@Param("levelnum") String levelnum,@Param("zhen") String zhen,@Param("cun") String cun,@Param("hosnumstr") String hosnumstr){
          try {
        	   String[] hosnumArray = hosnumstr.split(",");
               List list = mbPopulationService.findPopulation(name,sex,age1,age2,idnumber,phone,state,local,levelnum,zhen,cun,hosnumstr,hosnumArray);

               String exportName="人员信息";
               if("迁入".equals(state)){
            	   exportName="迁入人员信息";
               }else if("迁出".equals(state)){
            	   exportName="迁出人员信息";
               }else if("死亡".equals(state)){
            	   exportName="死亡人员信息";
               }
               ExcelWriter writer = ExcelUtil.getWriter();
               writer.addHeaderAlias("name", "姓名");
               writer.addHeaderAlias("age", "年龄");
               writer.addHeaderAlias("sex", "性别");
               writer.addHeaderAlias("birthday", "出生日期");
               writer.addHeaderAlias("credentials", "证件类别");
               writer.addHeaderAlias("idnumber", "证件号");
               writer.addHeaderAlias("marriage", "婚姻状态");
               writer.addHeaderAlias("telephony", "联系电话");
               writer.addHeaderAlias("newaddress", "现住地址");
               writer.addHeaderAlias("address", "户籍地址");
               writer.addHeaderAlias("urgentname", "紧急联系人姓名");
               writer.addHeaderAlias("urgentphone", "紧急联系人电话");
               writer.addHeaderAlias("local", "来源");
               writer.addHeaderAlias("chronic", "慢病信息");
               writer.addHeaderAlias("state", "状态");
               writer.addHeaderAlias("familyrole", "家庭角色");
               writer.addHeaderAlias("householder", "是否为户主");
               writer.addHeaderAlias("nation", "民族");
               writer.addHeaderAlias("nationality", "国籍");
               writer.addHeaderAlias("bloodtype", "血型");
               writer.addHeaderAlias("education", "文化程度");
               writer.addHeaderAlias("houseNumber" , "门牌号");

               writer.setOnlyAlias(true);
               writer.write(list,true);
               response.setContentType("application/vnd.ms-excel;charset=utf-8");
               String fileName = new String(exportName.getBytes(StandardCharsets.UTF_8),StandardCharsets.ISO_8859_1);
               response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
               ServletOutputStream out = null;
               try {
                    out = response.getOutputStream();
                    writer.flush(out, true);
               } catch (IOException e) {
                    e.printStackTrace();
               } finally {
                    // 关闭writer，释放内存
                    writer.close();
               }
               //此处记得关闭输出Servlet流
               IoUtil.close(out);

          } catch (Exception e) {
               e.printStackTrace();
          }
     }


     /**
      * 导入人口excel
      * @param file
      */
     @OperLog(operDesc = "导入人口excel",operType = "导入")
     @RequestMapping("/importExcel")
     public void importExcel(MultipartFile file,@Param("id") int id) throws Exception{
          if (file.isEmpty()){
               return ;
          }
          List<MbPopulationTemplete> list = importDataPro(file);
          List<MbPopulationTemplete> newlist=new ArrayList<MbPopulationTemplete>();
          list.forEach(mbPopulationTemplete -> {
               //获取拼音首字母
               HanyuPinyinOutputFormat format = new HanyuPinyinOutputFormat();
               //拼音大写
               format.setCaseType(HanyuPinyinCaseType.UPPERCASE);
               //不带声调
               format.setToneType(HanyuPinyinToneType.WITHOUT_TONE);
               try {
                    String letter = null;
                    letter = PinyinHelper.toHanYuPinyinString(mbPopulationTemplete.getName(), format, ",", true).substring(0,1);
                    mbPopulationTemplete.setFirstLetter(StringUtils.upperCase(letter));
               } catch (BadHanyuPinyinOutputFormatCombination badHanyuPinyinOutputFormatCombination) {
                    badHanyuPinyinOutputFormatCombination.printStackTrace();
               }

               newlist.add(mbPopulationTemplete);
          });
          mbPopulationService.batchInser(newlist,id);
     }

     /**
      * 导出模板
      */
     @OperLog(operDesc = "导出模板",operType = "导出")
     @RequestMapping("/getImportPopulationTemplate")
     public void getImportPopulationTemplate(HttpServletResponse response){
          ExcelWriter writer = ExcelUtil.getWriter();
          writer.addHeaderAlias("name", "姓名");
          writer.addHeaderAlias("age", "年龄");
          writer.addHeaderAlias("sex", "性别");
          String sex[]= {"男","女"};
          writer.addValidationData(addSelect(writer,sex,1,2));
          writer.addHeaderAlias("birthday", "出生日期");
          writer.addHeaderAlias("credentials", "证件类别");
          writer.addHeaderAlias("idnumber", "证件号");
//          writer.addHeaderAlias("marriage", "婚姻状态");
//          String marriage[] = {"已婚","未婚","未知"};
//          writer.addValidationData(addSelect(writer,marriage,1,6));
          writer.addHeaderAlias("telephony", "联系电话");
//          writer.addHeaderAlias("newaddress", "现住地址");
//          writer.addHeaderAlias("address", "户籍地址");
//          writer.addHeaderAlias("urgentname", "紧急联系人姓名");
//          writer.addHeaderAlias("urgentphone", "紧急联系人电话");
          writer.addHeaderAlias("local", "来源");
          String[] local = {"本地", "外地"};
          writer.addValidationData(addSelect(writer,local,1,7));
//          writer.addHeaderAlias("chronic", "慢病信息");
//          writer.addHeaderAlias("state", "状态");
//          writer.addHeaderAlias("familyrole", "家庭角色");
//          writer.addHeaderAlias("householder", "是否为户主");
//          writer.addHeaderAlias("nation", "民族");
//          writer.addHeaderAlias("nationality", "国籍");
//          writer.addHeaderAlias("bloodtype", "血型");
//          writer.addHeaderAlias("education", "文化程度");
//          writer.addHeaderAlias("houseNumber", "门牌号");
          writer.addHeaderAlias("height", "身高");
          writer.addHeaderAlias("weight", "体重");
          writer.addHeaderAlias("superiorNameOne","所属一级");
          writer.addHeaderAlias("superiorNameTwo","所属二级");
          writer.addHeaderAlias("superiorNameThree","所属三级");


          List<Codelist> xianlist =  codelistService.getHospital();
          List<Codelist> zhenlist =  codelistService.getZhenHospital();
          List<Codelist> chunlist =  codelistService.getChunHospital();
          String[] superiorNameOne = xianlist.stream().map(v -> v.getCodeName()).toArray(String[]::new);
          String[] superiorNameTwo = zhenlist.stream().map(v -> v.getCodeName()).toArray(String[]::new);
          String[] superiorNameThree = chunlist.stream().map(v -> v.getCodeName()).toArray(String[]::new);
          writer.addValidationData(addSelect(writer,superiorNameOne,1,10));
          writer.addValidationData(addSelect(writer,superiorNameTwo,1,11));
          writer.addValidationData(addSelect(writer,superiorNameThree,1,12));


          List list = new ArrayList();
          list.add(new MbPopulationTemplete());
          writer.setOnlyAlias(true);
          writer.write(list,true);
          response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
          String fileName = new String("人员信息".getBytes(StandardCharsets.UTF_8),StandardCharsets.ISO_8859_1);
          response.setHeader("Content-Disposition", "attachment;filename="+ fileName+".xls");
          ServletOutputStream out = null;
          try {
               out = response.getOutputStream();
               writer.flush(out, true);
          } catch (IOException e) {
               e.printStackTrace();
          } finally {
               writer.close();
          }
          IoUtil.close(out);
     }

     public List<MbPopulation> importData(MultipartFile file) throws IOException{
          List<MbPopulation> data = new ArrayList();
//          ExcelUtil.readBySax(file.getInputStream(),-1,new RowHandler() {
//               @Override
//               public void handle(int i, long l, List<Object> list) {
//                    list.removeAll(Collections.singleton(null));
//                    if(l > 0){
//                         MbPopulation mbPopulation = new MbPopulation();
//                         mbPopulation.setName(list.get(0).toString());
//                         mbPopulation.setAge(list.get(1).toString());
//                         mbPopulation.setSex(list.get(2).toString());
//                         mbPopulation.setBirthday(list.get(3).toString());
//                         mbPopulation.setCredentials(list.get(4).toString());
//                         mbPopulation.setIdnumber(list.get(5).toString());
//                         mbPopulation.setMarriage(list.get(6).toString());
//                         mbPopulation.setTelephony(list.get(7).toString());
//                         mbPopulation.setNewaddress(list.get(8).toString());
//                         mbPopulation.setAddress(list.get(9).toString());
//                         mbPopulation.setUrgentname(list.get(10).toString());
//                         mbPopulation.setUrgentphone(list.get(11).toString());
//                         mbPopulation.setLocal(list.get(12).toString());
//                         mbPopulation.setChronic(list.get(13).toString());
//                         mbPopulation.setState(list.get(14).toString());
//                         mbPopulation.setFamilyrole(list.get(15).toString());
//                         mbPopulation.setHouseholder(list.get(16).toString());
//                         mbPopulation.setNation(list.get(17).toString());
//                         mbPopulation.setNationality(list.get(18).toString());
//                         mbPopulation.setBloodtype(list.get(19).toString());
//                         mbPopulation.setEducation(list.get(20).toString());
//
//                         data.add(mbPopulation);
//                    }
//               }
//          });
          InputStream inputStream = file.getInputStream();
          ExcelReader reader = ExcelUtil.getReader(inputStream);

          reader.addHeaderAlias("姓名", "name");
          reader.addHeaderAlias("年龄","age");
          reader.addHeaderAlias("性别","sex");
          reader.addHeaderAlias("出生日期","birthday");
          reader.addHeaderAlias("证件类别", "credentials");
          reader.addHeaderAlias("证件号","idnumber");
          reader.addHeaderAlias("婚姻状态","marriage");
          reader.addHeaderAlias( "联系电话","telephony");
          reader.addHeaderAlias("现住地址","newaddress");
          reader.addHeaderAlias("户籍地址","address");
          reader.addHeaderAlias("紧急联系人姓名","urgentname");
          reader.addHeaderAlias("紧急联系人电话","urgentphone");
          reader.addHeaderAlias("来源","local");
          reader.addHeaderAlias("慢病信息","chronic");
          reader.addHeaderAlias("状态","state");
          reader.addHeaderAlias("家庭角色","familyrole");
          reader.addHeaderAlias("是否为户主","householder");
          reader.addHeaderAlias("民族","nation");
          reader.addHeaderAlias("国籍","nationality");
          reader.addHeaderAlias( "血型", "bloodtype");
          reader.addHeaderAlias("文化程度","education");
          reader.addHeaderAlias("门牌号", "houseNumber");

          data = reader.readAll(MbPopulation.class);

          return data;
     }


     public List<MbPopulationTemplete> importDataPro(MultipartFile file) throws IOException{
          List<MbPopulationTemplete> data = new ArrayList();
          InputStream inputStream = file.getInputStream();
          ExcelReader reader = ExcelUtil.getReader(inputStream);

          reader.addHeaderAlias("姓名", "name");
          reader.addHeaderAlias("年龄","age");
          reader.addHeaderAlias("性别","sex");
          reader.addHeaderAlias("出生日期","birthday");
          reader.addHeaderAlias("证件类别", "credentials");
          reader.addHeaderAlias("证件号","idnumber");
          reader.addHeaderAlias("婚姻状态","marriage");
          reader.addHeaderAlias( "联系电话","telephony");
          reader.addHeaderAlias("现住地址","newaddress");
          reader.addHeaderAlias("户籍地址","address");
          reader.addHeaderAlias("紧急联系人姓名","urgentname");
          reader.addHeaderAlias("紧急联系人电话","urgentphone");
          reader.addHeaderAlias("来源","local");
          reader.addHeaderAlias("慢病信息","chronic");
          reader.addHeaderAlias("状态","state");
          reader.addHeaderAlias("家庭角色","familyrole");
          reader.addHeaderAlias("是否为户主","householder");
          reader.addHeaderAlias("民族","nation");
          reader.addHeaderAlias("国籍","nationality");
          reader.addHeaderAlias( "血型", "bloodtype");
          reader.addHeaderAlias("文化程度","education");
          reader.addHeaderAlias("门牌号", "houseNumber");
          reader.addHeaderAlias("身高", "height");
          reader.addHeaderAlias("体重", "weight");
          reader.addHeaderAlias("所属一级", "superiorNameOne");
          reader.addHeaderAlias("所属二级", "superiorNameTwo");
          reader.addHeaderAlias("所属三级", "superiorNameThree");

          data = reader.readAll(MbPopulationTemplete.class);

          return data;
     }

     @RequestMapping("/findByNameAndPhone")
     public Map findByNameAndPhone(@RequestHeader("level") String level,@Param("name") String name,@Param("phone") String phone,@Param("id") String id){
          return mbPopulationService.findByNameAndPhone(id,name,phone,level);
     }

     @PostMapping("/saveArchives")
     public int saveArchives(@RequestBody SaveArchivesVo saveArchivesVo){
          System.out.println("输出VO：：：："+saveArchivesVo);
          return mbPopulationService.saveArchives(saveArchivesVo);
     }


     @RequestMapping("/updateCheckTime")
     public int updateCheckTime(@Param("id") String id ,@Param("idnumber") String idnumber){
          SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
          String checkTime = sdf.format(new Date());
          return mbPopulationService.updateCheckTime(checkTime,id,idnumber);
     }



}
